package com.icangya.DAO.impl;


import com.icangya.DAO.IUserFootDAO;
import com.icangya.Utils.SQLUtilt;
import com.icangya.domain.User;
import org.hibernate.HibernateException;
import org.hibernate.Query;
import org.hibernate.Session;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.orm.hibernate5.HibernateCallback;
import org.springframework.stereotype.Repository;

import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.List;

import static com.icangya.Value.StaticValue.PHONE_PAGE_ROW;


@Repository
public class UserFootDAOImpl extends  BaseDAO<User> implements IUserFootDAO{


    Logger logger = LoggerFactory.getLogger(this.getClass());

    @Override
    public User selectUserByOpenId(String openId) {
        String hql="FROM User user WHERE user.openId = ?";

        List<User> list = (List<User>) getHibernateTemplate().find(hql, openId);
        if (list != null && list.size() > 0)
            return list.get(0);
        return null;
    }

    @Override
    public Object[] getNickName(String openId) {

        String hql="select u.nickName,u.headImgUrl from User u where u.openId=?";
        Session session=  getHibernateTemplate().getSessionFactory().getCurrentSession();
        Query query=  session.createQuery(hql);
        query.setParameter(0,openId);
        List<Object[]> list= query.list();
        if(list.size()>0)
            return list.get(0);
        return null;
    }

    @Override
    public List<Object[]> getProxyUserId(long proxyId) {
        String hql="select u.id,u.nickName from User u where u.proxyId=?";
        Session session=  getHibernateTemplate().getSessionFactory().getCurrentSession();
        Query query=  session.createQuery(hql);
        query.setParameter(0,proxyId);
        List<Object[]> list= query.list();
        if(list.size()>0)
            return list;
        return null;
    }

    // 获取所有的用户信息    不查询二维码   添加代理得到时候重新生成二维码
    @Override
    public List<Object[]> findAllUser() {

        String hql="select u.openId,u.nickName,u.headImgUrl,u.createTime,u.parentId,u.proxyId,u.id from User u ";
        Session session=  getHibernateTemplate().getSessionFactory().getCurrentSession();
        Query query=  session.createQuery(hql);
        List<Object[]> list= query.list();
        if(list.size()>0)
            return list;
        return null;

    }

    @Override
    public User selectUserByUid(Long uid) {
        String hql="FROM User user WHERE user.id = ?";

        List<User> list = (List<User>) getHibernateTemplate().find(hql, uid);
        if (list != null && list.size() > 0)
            return list.get(0);
        return null;
    }

    /**
     * 获得用户的一级代理信息
     * @param uid
     * @return
     */
    public List getUserFirstLevel(Long uid){

        String hql="FROM User user where user.parentId=?";
        List<User> list= (List<User>) getHibernateTemplate().find(hql,uid);
            if(list.size()>0)
                return list;
        return null;
    }

    //获得用户二级代理



    @Override
    public Long getUserCount(Long uid) {

        String hql="select  count(user) FROM User user where user.parentId=?";

       List list= getHibernateTemplate().find(hql,uid);
        if (list == null || list.size() == 0)
            return 0L;
        Long value = (Long) list.get(0);
        return value;
    }

    @Override
    public List getAllUser() {
        String hql=" FROM User user ";
        List<User> users= (List<User>) getHibernateTemplate().find(hql);
        if(users.size()>0)
            return users;
        return null;
    }

//查询用户信息         不查询二维码
    @Override
    public List getAdminAllUser(int startPage,long proxyId) {
        //        String hql="select u.openId,u.nickName,u.headImgUrl,u.createTime,u.parentId,u.proxyId from User u ";
//        String count = "SELECT COUNT(user) FROM User user ";
//        Long total = (Long) getHibernateTemplate().find(count).get(0);
//        return SQLUtilt.getIstance().doPage(getHibernateTemplate(), hql, total, 1, PHONE_PAGE_ROW);
        //String hql = "FROM User userdo ";
        String hql="select u.id,u.openId,u.nickName,u.headImgUrl,u.createTime,u.parentId,u.proxyId from User u  where u.id !="+proxyId+" and   u.proxyId ="+proxyId;
        String count = "SELECT COUNT(u) from User u  where u.id !="+proxyId+" and   u.proxyId ="+proxyId;
        if(proxyId==1){
            //系统查询所有客户
            hql="select u.id,u.openId,u.nickName,u.headImgUrl,u.createTime,u.parentId,u.proxyId from User u";
            count="SELECT COUNT(user) FROM User user ";
        }
        Long total = (Long) getHibernateTemplate().find(count).get(0);
        return SQLUtilt.getIstance().doPage(getHibernateTemplate(), hql, total, startPage, PHONE_PAGE_ROW);

    }

    @Override
    public User checkUserById(long uid,long proxyId) {

        List<User> list=new ArrayList<User>();
        String hql="";
        if(proxyId!=1){
             hql="FROM User user WHERE user.id = ? and user.proxyId=? and user.id !=?";
            list = (List<User>) getHibernateTemplate().find(hql, uid,proxyId,proxyId);
        }

        if(proxyId==1){
            hql="FROM User user WHERE user.id = ? and user.id!=? ";
             list= (List<User>) getHibernateTemplate().find(hql, uid,proxyId);
        }

        if (list != null && list.size() > 0)
            return list.get(0);
        return null;

    }


    @Override
    public boolean updateProxyId(long id,Long proxyId) {


//        String hql="update Admin a set a.leftNum=a.leftNum+2 where a.leftNum>=? and a.id !=?";
        int result = getHibernateTemplate().execute(new HibernateCallback<Integer>() {
            @Override
            public Integer doInHibernate(Session session) throws HibernateException {
                String hql = " update User user set user.proxyId = ? where user.id = ? ";

                Query query= session.createQuery(hql);
                query.setParameter(0,id);
                if(proxyId !=null)
                    query.setParameter(0,proxyId);

                query.setParameter(1,id);
                int ret=query.executeUpdate();

                return ret;
            }
        });
        if (result > 0)
            return true;
        return false;
    }



    //代理下面所有用户当天的充值记录
    @Override
    public Double getAgentRechargeMoney(long proxyId,  Timestamp startTime,Timestamp endTime) {


//        select sum(recharge.money)  from User user,recharge_record recharge where user.openId=recharge.openId
//        and to_days(recharge.recharge_time) = to_days(now())  and user.proxy_id=2
//        String hql="select sum(recharge.money)  from User user,RechargeRecord recharge where user.openId=recharge.openId  " +
//                " and to_days(recharge.rechargeTime) = to_days(?)  and user.proxyId=? ";

        //代理团队俩个时间段内的充值记录
        //        select sum(recharge.money)  from user u,recharge_record recharge where u.openId=recharge.openId and
//        recharge.recharge_time between '2017-7-1 11:18:54' and '2017-8-11 23:18:54'  and u.proxy_id=12   -- 俩个时间段的充值记录


      String hql="  select sum(recharge.money)  from User user,RechargeRecord recharge where  user.openId=recharge.openId  " +
              " and recharge.rechargeTime between   ?  and  ?   and user.proxyId=? ";


        return getAgentMoney(hql,proxyId,startTime,endTime);
    }

    //代理团队用户所有的支出金额
    @Override
    public Double getAgentCommMoney(long proxyId,  Timestamp startTime,Timestamp endTime) {
//        select sum(ex.expend_money)  from  user u,expend_record  ex where u.openid=ex.openid
//        and to_days(ex.expend_time) = to_days(now())   and  u.proxy_id =12
//            String hql="select sum(ex.expendMoney)  from User user,ExpendRecord  ex where user.openId=ex.openId " +
//                    "    and to_days(ex.expendTime) = to_days(?)    and  user.proxyId =?";

            //代理团队指定时间内的支出总额
//        select sum(ex.expend_money)  from  user u,expend_record  ex where u.openid=ex.openid
//        and ex.expend_time  between '2017-7-1 11:18:54' and now()  and  u.proxy_id =12
        //代理团队指定时间内的支出总额
//        select sum(ex.commission_money)  from  fb_user u,commission_record  ex where u.openid=ex.openid
//        and ex.commission_time  between '2017-7-1 11:18:54' and now()  and  u.proxy_id =18
        String hql="select sum(cm.commissionMoney)  from User user,CommissionRecord  cm where user.openId=cm.openId " +
               " and cm.commissionTime between  ? and  ?    and  user.proxyId =?";

        return getAgentMoney(hql,proxyId,startTime,endTime);
    }

//代理团队的佣金提现金额
    @Override
    public Double getAgentCashComm(long proxyId, Timestamp startTime,Timestamp endTime) {

//        String hql="select sum(ca.money)  from User user,CashRegister  ca where user.openId=ca.openId " +
//                "    and to_days(ca.createTime) = to_days(?)    and  user.proxyId =?";
//        select sum(money)  from user ,cash_register  ca where `user`.openid=ca.openid
//        and to_days(ca.create_Time) = to_days(now())    and  user.proxy_id =2

        //代理团队指定时间内的佣金提现总额
//        select sum(money)  from user u,cash_register  ca where u.openid=ca.openid
//        and ca.create_Time between '2017-7-1 11:18:54' and now()    and u.proxy_id =12

        String hql="select sum(ca.money)  from User user,CashRegister  ca where user.openId=ca.openId " +
                "    and ca.createTime between ? and ?   and  user.proxyId =?";
        return getAgentMoney(hql,proxyId,startTime,endTime);
    }

    Double getAgentMoney(String hql, long proxyId, Timestamp startTime,Timestamp endTime){
        Session session=  getHibernateTemplate().getSessionFactory().getCurrentSession();
        Query query=  session.createQuery(hql);
        query.setParameter(0,startTime);
        query.setParameter(1,endTime);
        query.setParameter(2,proxyId);
        List list= query.list();
        logger.info(hql+"....................."+list.get(0));

        if(list.get(0)==null)
            return 0.0;

        return (Double) list.get(0);
    }

    //修改用户的代理id
    @Override
    public int updateUserProxyId(long proxyId, long uid) {
        String hql = " update User user set user.proxyId = ? where user.proxyId= ? ";
        Session session=  getHibernateTemplate().getSessionFactory().getCurrentSession();
       Query query= session.createQuery(hql);
          query.setParameter(0,uid);
          query.setParameter(1,proxyId);
        int result=  query.executeUpdate();

        return result;
    }




}
